package net.comm.user.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class ContactsServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Contacts where UserID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Contacts (UserID, Mobile, Tel, Famtel,Fax, QQ, Msn, email,Addr, Postcode," +
				" Offwebsite, Perwebsite, Blog, MTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Contacts  set Mobile=?,  Tel=? ,  Famtel=? ,  Fax=? ,  QQ=?,  Msn=? ," +
				"  email=?, Addr=? ,  Postcode=? , Offwebsite=?,  Perwebsite=?,  Blog=?, MTime=?   where UserID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select UserID, Mobile, Tel, Famtel,Fax, QQ, Msn, email, Addr, Postcode, Offwebsite, " +
				" Perwebsite, Blog, MTime from Contacts where UserID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select UserID,Mobile, Tel, Famtel,Fax, QQ, Msn, email, " +
				"Addr, Postcode, Offwebsite, Perwebsite, Blog,  MTime " +
				" from Contacts");
		
		sb.append(" where UserID=?  Order by MTime desc ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select UserID,  Mobile, Tel, Famtel,Fax, QQ, Msn,  email,Addr, " +
				"Postcode, Offwebsite, Perwebsite, Blog, MTime from Contacts");
		
		sb.append(" where UserID=? ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	

	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Contacts";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Contacts where UserID=? ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
